-- DROP FUNCTION timewindow(interval, integer);
CREATE OR REPLACE FUNCTION bintime(timestamp with time zone, interval)
  RETURNS timestamp with time zone AS
$BODY$
SELECT to_timestamp(
          floor(
              1000*extract('epoch' from $1) / (1000*extract('epoch' from $2))
          ) * extract('epoch' from $2)
       )
$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;
    COMMENT ON FUNCTION bintime(timestamp with time zone, interval)
 IS 'Round a timestamp (arg1) to the nearest interval (arg2)';

-- DROP FUNCTION timewindow(interval, numeric);
CREATE OR REPLACE FUNCTION timewindow(interval, numeric)
  RETURNS interval AS
$BODY$
SELECT (floor((1000*extract('epoch' from $1)/$2))/1000.0 || ' seconds')::interval
$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;

COMMENT ON FUNCTION timewindow(interval, numeric)
IS 'Divide a time interval by a numeric.';

-- Define an operator so that intervals can be divided by integers.
-- DROP OPERATOR /("interval", numeric);
CREATE OPERATOR /(
  PROCEDURE = timewindow,
  LEFTARG = interval,
  RIGHTARG = numeric);


CREATE OR REPLACE FUNCTION timeintervaldivide(interval, interval)
  RETURNS real AS
$BODY$
SELECT (extract('epoch' from $1)/extract('epoch' from $2))::real
$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;
  COMMENT ON FUNCTION timeintervaldivide(interval, interval)
IS 'Divide one interval by another. Return the quotient as a real number.';

-- Define an operator allowing interval division.
-- DROP OPERATOR /("interval", numeric);
CREATE OPERATOR /(
  PROCEDURE = timeintervaldivide,
  LEFTARG = interval,
  RIGHTARG = interval);


-- drop function timezoneof(text);
create or replace function timezoneof(text)
returns text as
$BODY$
SELECT 'UTC' || extract('hour' from interval) FROM (
select ($1::timestamptz::timestamp - timezone('UTC', $1::timestamptz)) - ($1::timestamptz - $1::timestamp) as interval
) i
$BODY$
  LANGUAGE 'sql' IMMUTABLE;



-- Type representing a starttime and endtime pair
CREATE TYPE timerange AS (
t1 timestamptz, t2 timestamptz
);


--DROP FUNCTION timerange(timestamp with time zone, timestamp with time zone, interval);
CREATE OR REPLACE FUNCTION timerange(timestamp with time zone, timestamp with time zone, interval)
  RETURNS SETOF timerange AS
$BODY$
declare
        s alias for $1;
        e alias for $2;
        binsize alias for $3;
        b timestamptz;
        c timestamptz;
        ret record;
begin
        b := s;
        while b <= e
        LOOP
                select b + binsize into c;
                ret := row(b,CASE WHEN c < e THEN c ELSE e END);
                return next ret;
                b := c;
        END LOOP;
        return;
end;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;
   COMMENT ON FUNCTION timerange(timestamp with time zone, timestamp with time zone, interval)
 IS 'Generate a set of timeranges between starttime (arg1) and endtime (arg2), each of width binsize (arg3).  The last record always ends with the endtime, i.e., if the interval does not divide evenly into (endtime - starttime).  This fact also implies that if interval > (endtime - starttime), a single record (starttime, endtime) will be returned.';


--DROP FUNCTION timerange(timestamp without time zone, timestamp without time zone, real);
CREATE OR REPLACE FUNCTION timerange(timestamp with time zone, timestamp with time zone, real)
  RETURNS SETOF timerange AS
$BODY$
SELECT * FROM timerange($1, $2, ($3 || ' seconds')::interval);
$BODY$
  LANGUAGE 'sql' IMMUTABLE;
 COMMENT ON FUNCTION timerange(timestamp with time zone, timestamp with time zone, real)
 IS 'Generate a set of timeranges between starttime (arg1) and endtime (arg2), each of size binsize seconds (arg3)';


--DROP FUNCTION timerange(timestamp with time zone, timestamp with time zone, integer);
CREATE OR REPLACE FUNCTION timerange(timestamp with time zone, timestamp with time zone, integer)
  RETURNS SETOF timerange AS
$BODY$
SELECT * from timerange($1, $2, ($2 - $1)/$3);
$BODY$
  LANGUAGE 'sql' IMMUTABLE;
   COMMENT ON FUNCTION timerange(timestamp with time zone, timestamp with time zone, integer)
 IS 'Generate a set of N (arg3) timeranges between starttime (arg1) and endtime (arg2)';

-- DROP FUNCTION timerange(timestamp with time zone, timestamp with time zone);
CREATE OR REPLACE FUNCTION timerange(timestamp with time zone, timestamp with time zone)
  RETURNS SETOF timerange AS
$BODY$
SELECT * from timerange($1, $2, '1 minute'::interval);
$BODY$
  LANGUAGE 'sql' IMMUTABLE;
   COMMENT ON FUNCTION timerange(timestamp with time zone, timestamp with time zone, interval)
 IS 'Generate a set of timeranges between starttime (arg1) and endtime (arg2) of one minute intervals';

